package com.szholly.pro.watergas_server.ps.service;

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.szholly.data.general.*;
import com.szholly.plug.form.excel.utils.StringUtil;
import com.szholly.pro.watergas_server.common.data.DataUtil;
import com.szholly.utils.spring.SpringBeanFactory;
import org.springframework.stereotype.Service;

/**
 * 耗材更换服务
 * @author Administrator
 *
 */
@Service
public class MaterialReplaceService {

	/**
	 * 获取耗材更换列表
	 * @param params
	 * @param pageInfo
	 * @return
	 * @throws Exception
	 */
	public List<RowBase> getMaterialReplaceList(Map<String, String> params, PageInfo pageInfo) throws Exception {
		List<GlobalParameter> listParam = new ArrayList<GlobalParameter>();
		String sql = " select f.*, t1.NAME as WWTP_NAME,t1.OUNAME, t2.NAME as STATION_NAME, t2.FLOW_NAME, t3.NAME as EQUIPMENT_NAME, t3.DEVICE_TYPENUM, t4.c_userealname as USER_NAME, "
				+ " d1.S_DOMAINCAPTION as MATERIAL_NAME_NAME, d2.S_DOMAINCAPTION as MATERIAL_UNIT_NAME, d3.S_DOMAINCAPTION as REPLACE_RESULT_NAME  "
				+ " from OP_MATE_REPLACE f " + " left join OPERATION_TASK t on ( t.TASK_CODE = f.TASK_ID ) "
				+ " left join (select pwa.name,pwa.exp_no,ou.name as OUNAME from BC_ENTERPRISE_ATTR pwa left join operations_unit ou on pwa.operator=ou.guid) t1 on ( t1.EXP_NO = t.WWTP_ID ) "
				+ " left join BC_SITE_INFO t2 on ( t2.EXP_NO = t.STATION_ID ) "
				+ " left join BC_SITE_DEVICE_RPT  t3 on ( t3.DEVICE_NUM = f.DEVICE_NUMBER ) "
				+ " left join safe_user t4 on ( t4.s_userid = f.REPLACE_PERSON ) "
				+ " left join DATA_DEFULATDOMAIN d1 on ( d1.S_DOMAINNAME = '部件耗材' AND d1.S_DOMAINCODE = f.MATERIAL_TYPE) "
				+ " left join DATA_DEFULATDOMAIN d2 on ( d2.S_DOMAINNAME = 'OPERATION_MATERIAL_UNIT' AND d2.S_DOMAINCODE = f.MATERIAL_UNIT) "
				+ " left join DATA_DEFULATDOMAIN d3 on ( d3.S_DOMAINNAME = 'OPERATION_REPLACE_RESULT' AND d3.S_DOMAINCODE = f.REPLACE_RESULT) "
				+ " where 1=1 ";

		if (params.containsKey("task_code")) {
			sql += " and f.TASK_ID = @task_code";
			listParam.add(new GlobalParameter("task_code", params.get("task_code")));
		} //else {
			if (params.containsKey("wwtp_id")) {
				sql += " and t2.WWTP_ID = @wwtp_id";
				listParam.add(new GlobalParameter("wwtp_id", params.get("wwtp_id")));
			}

			if (params.containsKey("flow_type")) {
				sql += " and t2.FLOW_TYPE = @flow_type";
				listParam.add(new GlobalParameter("flow_type", params.get("flow_type")));
			}
			
			if (params.containsKey("station_id")) {
				sql += " and t2.exp_no = @station_id";
				listParam.add(new GlobalParameter("station_id", params.get("station_id")));
			}
			
			if (params.containsKey("water_type")) {
				sql += " and t2.STATION_CATEGORY = @water_type";
				listParam.add(new GlobalParameter("water_type", params.get("water_type")));
			}

			if (params.containsKey("material_names")) {
				sql += " and f.MATERIAL_NAME in ("+params.get("material_names")+")";
			}

			if (params.containsKey("start")) {
				sql += " and f.REPLACE_DATE >= to_date('" + params.get("start")
						+ " 00:00:00', 'YYYY-MM-DD HH24:MI:SS')";
			}
			if (params.containsKey("end")) {
				sql += " and f.REPLACE_DATE <= to_date('" + params.get("end") + " 23:59:59', 'YYYY-MM-DD HH24:MI:SS')";
			}
		//}
		sql += " order by f.REPLACE_DATE desc";
		return DataUtil.getPageRow(sql, listParam, null,pageInfo);
	}
	
	/**
	 * 获取耗材更换列表(地表水)
	 * @param params
	 * @param pageInfo
	 * @return
	 * @throws Exception
	 */
	public List<RowBase> getSWMaterialReplaceList(Map<String, String> params, PageInfo pageInfo) throws Exception {
		List<GlobalParameter> listParam = new ArrayList<GlobalParameter>();
		String sql = " select f.*, t1.NAME as WWTP_NAME,t1.OUNAME, t2.NAME as STATION_NAME, t2.FLOW_NAME, t3.NAME as EQUIPMENT_NAME, t3.DEVICE_TYPENUM, t4.c_userealname as USER_NAME, "
				+ " d1.S_DOMAINCAPTION as MATERIAL_NAME_NAME, d2.S_DOMAINCAPTION as MATERIAL_UNIT_NAME, d3.S_DOMAINCAPTION as REPLACE_RESULT_NAME  "
				+ " from OP_MATE_REPLACE f " + " left join OPERATION_TASK t on ( t.TASK_CODE = f.TASK_ID ) "
				+ " left join (select pwa.name,pwa.exp_no,ou.name as OUNAME from BC_ENTERPRISE_ATTR pwa left join operations_unit ou on pwa.operator=ou.guid) t1 on ( t1.EXP_NO = t.WWTP_ID ) "
				+ " left join BC_SITE_INFO t2 on ( t2.EXP_NO = t.STATION_ID ) "
				+ " left join BC_SITE_DEVICE_RPT  t3 on ( t3.DEVICE_NUM = f.DEVICE_NUMBER ) "
				+ " left join safe_user t4 on ( t4.s_userid = f.REPLACE_PERSON ) "
				+ " left join DATA_DEFULATDOMAIN d1 on ( d1.S_DOMAINNAME = '地表水部件耗材' AND d1.S_DOMAINCODE = f.MATERIAL_TYPE) "
				+ " left join DATA_DEFULATDOMAIN d2 on ( d2.S_DOMAINNAME = 'OPERATION_MATERIAL_UNIT' AND d2.S_DOMAINCODE = f.MATERIAL_UNIT) "
				+ " left join DATA_DEFULATDOMAIN d3 on ( d3.S_DOMAINNAME = 'OPERATION_REPLACE_RESULT' AND d3.S_DOMAINCODE = f.REPLACE_RESULT) "
				+ " where 1=1 ";

		if (params.containsKey("task_code")) {
			sql += " and f.TASK_ID = @task_code";
			listParam.add(new GlobalParameter("task_code", params.get("task_code")));
		} //else {
			if (params.containsKey("wwtp_id")) {
				sql += " and t2.WWTP_ID = @wwtp_id";
				listParam.add(new GlobalParameter("wwtp_id", params.get("wwtp_id")));
			}

			if (params.containsKey("flow_type")) {
				sql += " and t2.FLOW_TYPE = @flow_type";
				listParam.add(new GlobalParameter("flow_type", params.get("flow_type")));
			}
			
			if (params.containsKey("station_id")) {
				sql += " and t2.exp_no = @station_id";
				listParam.add(new GlobalParameter("station_id", params.get("station_id")));
			}
			
			if (params.containsKey("water_type")) {
				sql += " and t2.STATION_CATEGORY = @water_type";
				listParam.add(new GlobalParameter("water_type", params.get("water_type")));
			}

			if (params.containsKey("material_names")) {
				sql += " and f.MATERIAL_NAME in ("+params.get("material_names")+")";
			}

			if (params.containsKey("start")) {
				sql += " and f.REPLACE_DATE >= to_date('" + params.get("start")
						+ " 00:00:00', 'YYYY-MM-DD HH24:MI:SS')";
			}
			if (params.containsKey("end")) {
				sql += " and f.REPLACE_DATE <= to_date('" + params.get("end") + " 23:59:59', 'YYYY-MM-DD HH24:MI:SS')";
			}
		//}
		sql += " order by f.REPLACE_DATE desc";
		return DataUtil.getPageRow(sql, listParam, null,pageInfo);
	}
	/**
	 * 获取耗材信息列表
	 * @param params
	 * @param pageInfo
	 * @return
	 * @throws Exception  
	 */
	public List<RowBase> qeuryOperationMaterial(Map<String, String> params, PageInfo pageInfo) throws Exception {
		List<GlobalParameter> listParam = new ArrayList<GlobalParameter>();
		String sql="select t1.GUID,t1.material_name,t1.material_unit,t1.material_code,t1.manufacturer,t1.material_typenum,t1.replace_cycle,t1.replace_count_year,t1.replace_count_quarter from OPERATION_MATERIAL t1 where 1=1";		
		sql+="  order by t1.material_name desc";
		return DataUtil.getPageRow(sql, listParam, null, pageInfo);
	}
	/**
	 * 获取耗材名称
	 * @param params
	 * @param pTable
	 * @return
	 * @throws Exception
	 */
	public List<RowBase> getMaterialNameService(Map<String, String> params,ITable pTable) throws Exception {
		String sql="select DISTINCT  material_name from OPERATION_MATERIAL ";
		List<GlobalParameter> listParam=new ArrayList<>();
		return DataUtil.getSelectRows(sql, listParam, pTable);
	}
	/**
	 * 获得编号
	 * @param params
	 * @param pTable
	 * @return
	 * @throws Exception
	 */
	public List<RowBase> getManufacterCodeService(Map<String, String> params,ITable pTable) throws Exception {
		String sql="select DISTINCT material_code from OPERATION_MATERIAL t where 1=1";
		List<GlobalParameter> listParam=new ArrayList<>();
		if (params.containsKey("material_name")) {
			sql += " and t.material_name = @material_name";
			listParam.add(new GlobalParameter("material_name", params.get("material_name")));
		} 
		return DataUtil.getSelectRows(sql, listParam, pTable);
	}
	/**
	 * 获得品牌名称
	 * @param params
	 * @param pTable
	 * @return
	 * @throws Exception
	 */
	public List<RowBase> getManufacterNameService(Map<String, String> params,ITable pTable) throws Exception {
		String sql="select DISTINCT manufacturer from OPERATION_MATERIAL ";
		List<GlobalParameter> listParam=new ArrayList<>();
		return DataUtil.getSelectRows(sql, listParam, pTable);
	}
	/**
	 * 获得型号
	 * @param params
	 * @param pTable
	 * @return
	 * @throws Exception
	 */
	public List<RowBase> getManufacterTypeService(Map<String, String> params,ITable pTable) throws Exception {
		String sql="select DISTINCT material_typenum from OPERATION_MATERIAL t where 1=1";
		List<GlobalParameter> listParam=new ArrayList<>();
		if (params.containsKey("manufacturer")) {
			sql += " and t.manufacturer = @manufacturer";
			listParam.add(new GlobalParameter("manufacturer", params.get("manufacturer")));
		} 
		return DataUtil.getSelectRows(sql, listParam, pTable);
	}
	/**
	 * 编辑耗材信息
	 * @param params
	 * @param pTable
	 * @throws Exception
	 */
	public void updateOperationMaterial(HashMap<String, Object> params,ITable pTable)throws Exception{	
		DataUtil.Update(pTable.getTableName(), params);
	}
	/**
	 * 新增耗材信息
	 * @param tableName
	 * @param params
	 */
	public void addOperationMaterial(String tableName,HashMap<String, Object> params){
		DataUtil.Create(tableName, params);
	}
	/**
	 * 获取耗材更换记录数
	 * @param params
	 * @return
	 */
	public int getReplacementCount(Map<String, String> params) {
		List<GlobalParameter> listParam = new ArrayList<GlobalParameter>();
		String sql = " select count(*) from OP_MATE_REPLACE f " + " left join OPERATION_TASK t on ( t.TASK_CODE = f.TASK_ID ) "
				+ " left join (select pwa.name,pwa.exp_no,ou.name as OUNAME from BC_ENTERPRISE_ATTR pwa left join operations_unit ou on pwa.operator=ou.guid) t1 on ( t1.EXP_NO = t.WWTP_ID ) "
				+ " left join BC_SITE_INFO t2 on ( t2.EXP_NO = t.STATION_ID ) "
				+ " left join BC_SITE_DEVICE_RPT  t3 on ( t3.DEVICE_NUM = f.DEVICE_NUMBER ) "
				+ " left join safe_user t4 on ( t4.s_userid = f.REPLACE_PERSON ) "
				+ " left join DATA_DEFULATDOMAIN d1 on ( d1.S_DOMAINNAME = 'OPERATION_MATERIAL_NAME' AND d1.S_DOMAINCODE = f.MATERIAL_NAME) "
				+ " left join DATA_DEFULATDOMAIN d2 on ( d2.S_DOMAINNAME = 'OPERATION_MATERIAL_UNIT' AND d2.S_DOMAINCODE = f.MATERIAL_UNIT) "
				+ " left join DATA_DEFULATDOMAIN d3 on ( d3.S_DOMAINNAME = 'OPERATION_REPLACE_RESULT' AND d3.S_DOMAINCODE = f.REPLACE_RESULT) "
				+ " where 1=1 ";

		if (params.containsKey("task_code")) {
			sql += " and f.TASK_ID = @task_code";
			listParam.add(new GlobalParameter("task_code", params.get("task_code")));
		} else {
			if (params.containsKey("wwtp_id")) {
				sql += " and f.WWTP_ID = @wwtp_id";
				listParam.add(new GlobalParameter("wwtp_id", params.get("wwtp_id")));
			}

			if (params.containsKey("flow_type")) {
				sql += " and t2.FLOW_TYPE = @flow_type";
				listParam.add(new GlobalParameter("flow_type", params.get("flow_type")));
			}
			
			if (params.containsKey("water_type")) {
				sql += " and t2.STATION_CATEGORY = @water_type";
				listParam.add(new GlobalParameter("water_type", params.get("water_type")));
			}

			if (params.containsKey("material_names")) {
				sql += " and f.MATERIAL_NAME in (@material_names)";
				listParam.add(new GlobalParameter("material_names", params.get("material_names")));
			}

			if (params.containsKey("start")) {
				sql += " and f.REPLACE_DATE >= to_date('" + params.get("start")
						+ " 00:00:00', 'YYYY-MM-DD HH24:MI:SS')";
			}
			if (params.containsKey("end")) {
				sql += " and f.REPLACE_DATE <= to_date('" + params.get("end") + " 23:59:59', 'YYYY-MM-DD HH24:MI:SS')";
			}
		}
		return DataUtil.getScaleInt(sql, listParam);
	}
	
	/**
	 * 获取备件耗材更换任务数据记录列表
	 * 
	 * @param params
	 * @return
	 */
	public List<RowBase> getMaterialReplaceDataList(Map<String, String> params) {
		if (params == null)
			return null;

		String strSTATION_ID = params.get("STATION_ID");
		String strJOB_ID = params.get("JOB_ID");
		String strSTART_TIME = params.get("START_TIME");
		String strEND_TIME = params.get("END_TIME");
		if (StringUtil.IsNullOrEmpty(strSTATION_ID) || StringUtil.IsNullOrEmpty(strJOB_ID))
			return null;

		String strSQL = " select t.TASK_CODE, t.TASK_STATUS, t.START_TIME as TASK_START_TIME, t.END_TIME as TASK_END_TIME, t.FINISH_TIME as TASK_FINISH_TIME,  \n"
				+ " t1.*, \n" + " t2.NAME as STATION_NAME, \n" + " t3.GROUP_NAME as GROUP_NAME, \n"
				+ " t4.C_USEREALNAME as USER_NAME \n" + " from OP_TASK t \n"
				+ " inner join OP_MATE_REPLACE t1 ON t1.TASK_ID = t.TASK_CODE \n" // dif
				+ " left join BC_SITE_INFO t2 on t2.EXP_NO = t.STATION_ID \n"
				+ " left join OC_GROUP t3 on t3.GUID = t.OPERATION_GROUP_ID \n"
				+ " left join SAFE_USER t4 on t4.S_USERID = t.FINISH_BY \n" + " where t.STATION_ID = '" + strSTATION_ID
				+ "' \n" + " AND t.JOB_ID = '" + strJOB_ID + "' \n"
				// + " AND t1.MONITOR_PARM = '" + strPARAM_ID + "' \n" //dif
				+ " AND t.START_TIME >= to_date('" + strSTART_TIME + " 00:00:00', 'yyyy-mm-dd hh24:mi:ss') \n"
				+ " AND t.START_TIME <= to_date('" + strEND_TIME + " 00:00:00', 'yyyy-mm-dd hh24:mi:ss') \n"
				+ " order by t.START_TIME desc ";

		ITableFactory tableFactory = (ITableFactory) SpringBeanFactory.getBean("TableFactory");
		ITable pTable = tableFactory.openTable("TASKQUERYDIC");
		List<RowBase> rcdList = DataUtil.getSelectRows(strSQL, null, pTable);
		return rcdList;
	}
	/**
	 * 获取仪器名称
	 * @param params
	 * @return
	 */
	public List<RowBase> StatisticsEquipment(Map<String, String> params,ITable pTable) {
		
		String sql="select t3.s_domaincaption device_name,  sum(t1.material_quantity) as replacecount, me.DEVICE_TYPENUM device_type,  me.MANUFACTURER device_band,me.monitor_pollutants device_code,me.device_count"
				  +" from OP_MATE_REPLACE t1 left join data_defulatdomain t2 on t2.s_domaincode = t1.material_type and t2.s_domainname = '供水耗材名称'"
				  +" left join (select t.monitor_pollutants,t.MANUFACTURER,t.DEVICE_TYPENUM,count(t.monitor_pollutants) as device_count from"
				  +" MONITOR_EQUIPMENT t where t.monitor_point in(select code from BC_SITE_INFO t1 where t1.station_category='3')"
				  +" group by t.monitor_pollutants,t.MANUFACTURER,t.DEVICE_TYPENUM)  me on me.monitor_pollutants = t2.type"
				  +" left join data_defulatdomain t3 on t3.s_domaincode = me.monitor_pollutants and t3.s_domainname = '检测设备代号'"
				  +" left join operation_material t4 on t4.material_code = t2.s_domaincode"
				  +" inner join BC_SITE_INFO ms on ms.code = t1.station_id and ms.station_category='3' " ;
		if(params.containsKey("station_id")) {
			sql += " and ms.code='"+params.get("station_id")+"'";
		}
		if (params.containsKey("start")) {//开始时间
			sql += " and t1.replace_date >= to_date('" + params.get("start")
					+ " 00:00:00', 'YYYY-MM-DD HH24:MI:SS')";
		}
		if (params.containsKey("end")) {//结束时间
			sql += " and t1.replace_date <= to_date('" + params.get("end") + " 23:59:59', 'YYYY-MM-DD HH24:MI:SS')";
		}
		if(params.containsKey("Other")){
			sql += " and t1.device_name like '%"+params.get("Other")+"%'";
		}else{
			sql += " and me.device_typenum is not null";
		}
		sql+=" group by me.monitor_pollutants, me.MANUFACTURER, me.device_count, me.DEVICE_TYPENUM,t3.s_domaincaption";
		List<GlobalParameter> listParam=new ArrayList<>();
		return DataUtil.getSelectRows(sql, listParam,pTable);
	}
	/**
	 * 获取故障仪器名称
	 * @param params
	 * @return
	 */
	public List<RowBase> StatisticsFaultEquipment(Map<String, String> params,ITable pTable) {
		
		String sql=" select count(t3.s_domaincaption) as FAULTCount, case when to_char(t5.s_domaincode) is not null then to_char(t5.s_domaincode) else  '-1' end as s_domaincode"
				+ " ,case when to_char(t5.s_domaincaption) is not null then to_char(t5.s_domaincaption) else  '其它' end as s_domaincaption from OP_ALAR_PROC t1"
         +" left join operation_task t2 on t2.task_code = t1.task_code"
         +" left join data_defulatdomain t3 on t3.s_domaincode = t1.exception_reason and t3.s_domainname = '供水异常故障项'"
         +" left join BC_SITE_DEVICE_RPT  t4 on t1.equipment_no = t4.device_num"
         +"  left join data_defulatdomain t5 on t5.s_domaincode =t4.monitor_pollutants and t5.s_domainname = '检测设备代号'  where 1=1 " ;
		if(params.containsKey("start")){
			sql += " and t1.createtime >= to_date('" + params.get("start")
			+ " 00:00:00', 'YYYY-MM-DD HH24:MI:SS')";
		}
		if(params.containsKey("end")){
			sql += " and t1.end_time <= to_date('" + params.get("end")
			+ " 23:59:59', 'YYYY-MM-DD HH24:MI:SS')";
		}
		sql+="  group by t5.s_domaincode,t5.s_domaincaption order by FAULTCount desc";
		List<GlobalParameter> listParam=new ArrayList<>();
		return DataUtil.getSelectRows(sql, listParam,pTable);
	}
	
	/**
	 * 获取仪器故障项
	 * @param params
	 * @return
	 */
	public List<RowBase> queryOperationAlarmProcess(HashMap<String, String> params){
		
		String sql="select count(t3.s_domaincaption) as FAULTCount,"
				+ "t3.s_domaincaption,case when to_char(t5.s_domaincode) is not null then to_char(t5.s_domaincode) else '-1' end as s_domaincode  from OP_ALAR_PROC t1 "
				+" left join  operation_task t2 on t2.task_code=t1.task_code"
				+" left join data_defulatdomain t3 on t3.s_domaincode=t1.exception_reason and t3.s_domainname='供水异常故障项'"
				+" left join BC_SITE_DEVICE_RPT  t4 on t1.equipment_no=t4.device_num"
				+" left join data_defulatdomain t5 on t5.s_domaincode=t4.monitor_pollutants and t5.s_domainname='检测设备代号' where 1=1 ";
			if(params.containsKey("station_id")) {
   				sql += " and ms.code='"+params.get("station_id")+"'";
			}
			if(params.containsKey("start")){
				sql += " and t1.createtime >= to_date('" + params.get("start")
				+ " 00:00:00', 'YYYY-MM-DD HH24:MI:SS')";
			}
			if(params.containsKey("end")){
				sql += " and t1.end_time <= to_date('" + params.get("end")
				+ " 23:59:59', 'YYYY-MM-DD HH24:MI:SS')";
			}
				sql+="  group by t3.s_domaincaption,t5.s_domaincaption,t5.s_domaincode,t3.s_domaincaption";
		List<GlobalParameter> listParam = new ArrayList<>();
		return DataUtil.getSelectRows(sql, listParam);
	}
	/**
	 * 故障统计
	 * @param params
	 * @param pTable
	 * @return
	 */
    public List<RowBase> queryFaultStatics(Map<String, String> params) {
		
		String sql=" select substr(t5.name,instr(t5.name,'、')+1,length(t5.name)-instr(t5.name,'、')) as NAME,t3.s_domaincaption,t1.faultcause,t1.exception_process"
				+ " ,t1.start_time , case when to_char(t1.process_result) = '1'  then '正常' else '不正常' end as process_result ,t1.createtime ,t1.end_time from OP_TASK t2"
         +" left join OP_ALAR_PROC t1 on t2.task_code=t1.task_code"
         +" left join data_defulatdomain t3 on t3.s_domaincode=t1.exception_reason and t3.s_domainname='供水异常故障项'"
         +" left join BC_SITE_DEVICE_RPT  t4 on t1.equipment_no=t4.device_num"
         +" left join BC_SITE_INFO t5 on t5.code = t2.station_id and t5.station_category='3' where 1=1 " ;
		if(params.containsKey("station_id")) {
				sql += " and t5.code='"+params.get("station_id")+"'";
		}
		if(params.containsKey("start")){
			sql += " and t1.createtime >= to_date('" + params.get("start")
			+ " 00:00:00', 'YYYY-MM-DD HH24:MI:SS')";
		}
		if(params.containsKey("end")){
			sql += " and t1.end_time <= to_date('" + params.get("end")
			+ " 23:59:59', 'YYYY-MM-DD HH24:MI:SS')";
		}
		sql+="  group by t5.name,t3.s_domaincaption,t1.faultcause,t1.exception_process ,t1.start_time ,t1.process_result ,t1.createtime,t1.end_time ";
		List<GlobalParameter> listParam=new ArrayList<>();
		return DataUtil.getSelectRows(sql, listParam);
	}
	
	/**
	 * 获取仪器耗材记录
	 * @param params
	 * @return
	 */
	public List<RowBase> StatisticsReplace(Map<String, String> params,ITable pTable) {	
	
		String sql=" select t.*,tt.materialcount from (select t3.s_domaincaption device_name,"
				  +" t4.replace_cycle,t4.replace_count_year, t4.replace_count_quarter,t4.material_unit,"
				  +" me.DEVICE_TYPENUM device_type, me.MANUFACTURER device_band, me.monitor_pollutants device_code,"
				  +" t4.material_code, t2.s_domaincaption material_name, sum(t1.material_quantity) material_count from OP_MATE_REPLACE t1"
				  +" left join data_defulatdomain t2 on t2.s_domaincode = t1.material_type and t2.s_domainname = '供水耗材名称'"
				  +" left join BC_SITE_DEVICE_RPT  me on me.Monitor_Point = t1.station_id  and me.monitor_pollutants = t2.type"
				  +" left join data_defulatdomain t3 on t3.s_domaincode = me.monitor_pollutants and t3.s_domainname = '检测设备代号'" 
				  +" left join operation_material t4 on t4.material_code = t2.s_domaincode where 1 = 1";
			if (params.containsKey("start")) {//开始时间
				sql += " and t1.replace_date >= to_date('" + params.get("start")
					+ " 00:00:00', 'YYYY-MM-DD HH24:MI:SS')";
			}
			if (params.containsKey("end")) {//结束时间
				sql += " and t1.replace_date <= to_date('" + params.get("end") + " 23:59:59', 'YYYY-MM-DD HH24:MI:SS')";
			}
			if(params.containsKey("Other")){
				sql += " and t1.device_name like '%"+params.get("Other")+"%'";
			}
			if(params.containsKey("station_id")) {
				sql += " and t1.station_id='"+params.get("station_id")+"'";
			}
				sql+="   group by me.monitor_pollutants, me.MANUFACTURER, me.DEVICE_TYPENUM, t2.s_domaincaption,t3.s_domaincaption,"
				    +" t4.replace_cycle, t4.replace_count_year,t4.replace_count_quarter, t4.material_code, t4.material_unit order by device_name asc)";
				sql+="t left join (select t4.material_code,sum(t1.material_quantity) materialcount"
					+" from OP_MATE_REPLACE t1 left join data_defulatdomain t2 on t2.s_domaincode = t1.material_type and t2.s_domainname = '供水耗材名称'"
					+" left join BC_SITE_DEVICE_RPT  me on me.Monitor_Point = t1.station_id and me.monitor_pollutants = t2.type"
					+" left join data_defulatdomain t3 on t3.s_domaincode = me.monitor_pollutants and t3.s_domainname = '检测设备代号'" 
					+" left join operation_material t4 on t4.material_code = t2.s_domaincode where 1 = 1";	
			if (params.containsKey("atStart")) {//开始时间
				sql += " and t1.replace_date >= to_date('" +params.get("atStart")+ "-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')";
			}
			if (params.containsKey("atEnd")) {//结束时间
				sql += " and t1.replace_date <= to_date('" + params.get("atEnd") + " 23:59:59', 'YYYY-MM-DD HH24:MI:SS')";
			}
			if(params.containsKey("Other")){
				sql += " and t1.device_name like '%"+params.get("Other")+"%'";
			}
			if(params.containsKey("station_id")) {
				sql += " and t1.station_id='"+params.get("station_id")+"'";
			}
				sql+=" group by me.monitor_pollutants, me.MANUFACTURER, me.DEVICE_TYPENUM, t2.s_domaincaption,"
				    +" t3.s_domaincaption, t4.replace_cycle, t4.replace_count_year,t4.replace_count_quarter,"
				    +" t4.material_code, t4.material_unit) tt on t.material_code=tt.material_code";
		List<GlobalParameter> listParam=new ArrayList<>();
		return DataUtil.getSelectRows(sql, listParam,pTable);
	}
}
